import mysql.connector
from mysql.connector import Error
from configparser import ConfigParser


# NOTE:
# 可用, 连的阿里云测试数据库


class MySQLVrlab:
    _instance = None

    def __init__(self, path_config):
        if self._instance is not None:
            return
        # 读取配置文件
        config = ConfigParser()
        config.read(path_config, encoding='utf-8')
        self.host = config.get('default', 'VRLAB_HOST')
        self.user = config.get('default', 'VRLAB_USER')
        self.password = config.get('default', 'VRLAB_PASSWORD')
        self.database = config.get('default', 'VRLAB_DATABASE')
        self.port = config.get('default', 'VRLAB_PORT')
        print("[MySQL setting] host:", self.host, ", user:", self.user, ", passwd:", self.password,
              ", db:", self.database, ", port:", self.port)
        self.connection = None

    def __new__(cls, path_config):
        # print("__new__")
        if cls._instance is None:
            instance = super().__new__(cls)
            instance.__init__(path_config)  # 如果需要初始化操作
            cls._instance = instance
        return cls._instance

    def connect(self):
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port
            )
            if self.connection.is_connected():
                print("成功连接到MySQL数据库")
        except Error as e:
            print(f"连接数据库时发生错误: {e}")

    def disconnect(self):
        if self.connection.is_connected():
            self.connection.close()
            print("MySQL连接已关闭")

    def select_scores(self):
        try:
            if not self.connection.is_connected():
                self.connect()
            cursor = self.connection.cursor()
            query = f'SELECT id, username, score FROM vrlab_steps_record WHERE title = "实验报告撰写与提交" AND username LIKE "mj_%" and score = "5";'
            # query = f'SELECT id, username, score FROM vrlab_steps_record WHERE title = "实验报告撰写与提交" AND username LIKE "mj_%";'
            # query = f'SELECT id, username, score FROM vrlab_steps_record WHERE title = "实验报告撰写与提交" AND username LIKE "mj_%" AND remarks = "无";'
            cursor.execute(query)
            records = cursor.fetchall()
            cursor.close()
            return records
        except Error as e:
            print(f"执行查询时发生错误: {e}")
            return None

    def update_remarks(self, remarks, rid):
        try:
            if not self.connection or not self.connection.is_connected():
                self.connect()
            cursor = self.connection.cursor()
            query = f'UPDATE vrlab_steps_record SET remarks = %s WHERE id = %s;'
            params = (remarks, rid)
            print(query, params)
            cursor.execute(query, params)
            self.connection.commit()
            cursor.close()
            print(f"成功更新 {cursor.rowcount} 条记录")
        except Error as e:
            print(f"执行更新时发生错误: {e}")




if __name__ == "__main__":

    # 创建 VRLabScores 对象
    mysqlVrlab = MySQLVrlab("../files/config.inf")

    # # 调用方法执行查询
    # mysqlVrlab.connect()
    # records = mysqlVrlab.select_scores()
    # mysqlVrlab.disconnect()
    #
    # # 输出查询结果
    # if records is not None:
    #     for row in records:
    #         print(row)  # 这里只是简单地打印每一行，你可以根据需要处理这些数据

    # 示例调用 update Remarks 方法
    mysqlVrlab.connect()
    result_mysql = {
        "part3": "1 (0.954)",
        "part4": "1 (0.954)",
        "part5": "1 (0.954)",
        "part6": "1 (0.954)",
        "part7": "1 (0.954)",
        "final": 5,
    }
    # mysqlVrlab.update_remarks(str(result_mysql), 693)
    mysqlVrlab.update_remarks("无", 879)
    mysqlVrlab.disconnect()